import os
import glob
import pandas as pd


# 收支报表审核服务类
class ReportReviewServer:

    def __init__(self, pz_json: dict, out_path, pre_path=None, now_path=None):
        self.pre_df = pd.read_csv(pre_path, encoding="GBK") if not pre_path is None else None
        self.now_df = pd.read_csv(now_path, encoding="GBK") if not now_path is None else None
        self.pz_json = pz_json
        self.nb_zb = pd.read_excel(r"./res/审核部分配置文件/年报指标.xlsx",engine='openpyxl')
        self.out_path = out_path

    # ******************************* 两年年报审核 ************************************* #
    def 调味品变化存疑(self):
        # N2872增长率高于
        N2872增长率高于 = float(self.pz_json.get("两年报表审核").get("调味品变化存疑").get("N2872增长率高于"))
        N2872人均低于 = float(self.pz_json.get("两年报表审核").get("调味品变化存疑").get("N2872人均低于"))
        N2872人均高于 = float(self.pz_json.get("两年报表审核").get("调味品变化存疑").get("N2872人均高于"))

        nb_pre = self.pre_df.copy()
        nb_now = self.now_df.copy()
        # 获取两年共同户和205相同(SID,205)
        SID_205 = pd.merge(nb_now, nb_pre, how="inner", on=["sID", "N0205"])["sID"].drop_duplicates()
        nb_pre = nb_pre[nb_pre["sID"].isin(SID_205)][["sID", "coun", "sCode", "N2872"]]
        nb_now = nb_now[nb_now["sID"].isin(SID_205)][["sID", "coun", "sCode", "N2872", "N0205"]]
        nb = pd.merge(nb_now, nb_pre, how="left", on="sID", suffixes=["", "_pre"])
        nb["N2872增长率"] = ((nb["N2872"] / nb["N2872_pre"]) - 1).abs()
        nb["N2872人均"] = nb["N2872"] / nb["N0205"]

        # 调味品存疑 >= 50%
        # temp_res = nb[nb["N2872比值"] >= 0.5]
        temp_res = nb[(nb["N2872增长率"] >= N2872增长率高于) | (nb["N2872人均"] < N2872人均低于) | (
                nb["N2872人均"] > N2872人均高于)]
        if len(temp_res):
            # 输出
            temp_res = temp_res[["sID", "coun", "sCode", "N2872", "N0205", "N2872_pre", "N2872人均", "N2872增长率"]]
            temp_res.rename(
                columns={
                    "N2872": "24年N2872",
                    "N2872_pre": "23年N2872"
                },
                inplace=True
            )
            temp_res.to_excel(f"{self.out_path}/调味品存疑.xlsx", index=False)
            return True
        else:
            # 无结果
            return False

    def 常住人口变动存疑(self):
        N0205差值高于 = float(self.pz_json.get("两年报表审核").get("常住人口变动存疑").get("N0205差值高于"))

        nb_pre = self.pre_df.copy()
        nb_now = self.now_df.copy()

        # 获取两年共同户
        SID_con = pd.merge(nb_now, nb_pre, how="inner", on="sID")["sID"].drop_duplicates()
        nb_pre = nb_pre[nb_pre["sID"].isin(SID_con)][["sID", "coun", "sCode", "N0205"]]
        nb_now = nb_now[nb_now["sID"].isin(SID_con)][["sID", "coun", "sCode", "N0205"]]
        nb = pd.merge(nb_now, nb_pre, how="left", on="sID", suffixes=["", "_pre"])
        nb["N0205差值"] = (nb["N0205"] - nb["N0205_pre"]).abs()
        temp_res = nb[nb["N0205差值"] > N0205差值高于]
        if len(temp_res):
            # 输出
            temp_res = temp_res[["sID", "coun", "sCode", "N0205", "N0205_pre", "N0205差值"]]
            temp_res.rename(
                columns={
                    "N0205": "24年N0205",
                    "N0205_pre": "23年N0205"
                },
                inplace=True
            )
            temp_res.to_excel(f"{self.out_path}/常住人口变动较大请核实.xlsx", index=False)
            return True
        else:
            return False

    def 养老金变动存疑(self):
        nb_pre = self.pre_df.copy()
        nb_now = self.now_df.copy()
        nb_pre["N480+N481+N483"] = nb_pre["N0480"] + nb_pre["N0481"] + nb_pre["N0483"]
        nb_now["N480+N481+N483"] = nb_now["N0480"] + nb_now["N0481"] + nb_now["N0483"]
        # 获取两年共同户
        nb_com = pd.merge(nb_now, nb_pre, how="inner", on=["sID", "N480+N481+N483"], suffixes=["", "_pre"])

        # 3883 增长率绝对值超过 20%
        # not_in = nb[(nb["N3883"] == 0) & (nb["N3883_pre"] == 0)]["sID"]
        # nb_com = nb_com[~nb_com.isin(not_in)]
        nb_com["N3883增长率"] = (nb_com["N3883"] / nb_com["N3883_pre"] - 1).abs()
        temp_res = nb_com[nb_com["N3883增长率"] > 0.2]
        if len(temp_res):

            # 输出
            temp_res = temp_res[
                ["sID", "coun", "sCode", "N0205", "N480+N481+N483", "N3883", "N3883_pre", "N3883增长率"]]
            temp_res.rename(
                columns={
                    "N3883": "24年N3883",
                    "N3883_pre": "23年N3883"
                },
                inplace=True
            )
            temp_res.to_excel(f"{self.out_path}/养老金变动存疑.xlsx", index=False)
            return True
        else:
            return False

    def 食品消费存疑(self):
        zb = self.nb_zb
        # zb["代码"] = zb["代码"].astype("int")
        nb_pre = self.pre_df.copy()
        nb_now = self.now_df.copy()

        # 获取两年共同户
        SID_con = pd.merge(nb_now, nb_pre, how="inner", on="sID")["sID"].drop_duplicates()
        nb_pre = nb_pre[nb_pre["sID"].isin(SID_con)]
        nb_now = nb_now[nb_now["sID"].isin(SID_con)]
        nb = pd.merge(nb_now, nb_pre, how="left", on="sID", suffixes=["", "_pre"])
        keys = list(zb["代码"])
        # 索引 指标中5802 -- 5912
        keys_5802 = keys.index(5802)
        keys_5912 = keys.index(5912)
        keys = keys[keys_5802:keys_5912 + 1]
        # 理想状态 都是相同的
        with pd.ExcelWriter(f"{self.out_path}/食品消费存疑.xlsx") as writer:
            for key in keys:

                sheet_name = list(zb[zb["代码"] == key]["指标名称"])[0].strip()
                key = str(key)
                temp_res = nb[["sID", "coun", "sCode", "N0205", "N" + key, "N" + key + "_pre"]].copy()

                temp_res["N" + key + "增长率"] = temp_res["N" + key] / temp_res["N" + key + "_pre"] - 1
                temp_res["N" + key + "增长率abs"] = temp_res["N" + key + "增长率"].abs()
                temp_res[f"N{key}人均"] = temp_res["N" + key] / temp_res["N0205"]
                # 设置为百分数表示

                temp_res = temp_res[temp_res["N" + key + "增长率abs"] > 0.3]
                temp_res = temp_res[["sID", "coun", "sCode", "N0205", "N" + key, "N" + key + "_pre", f"N{key}人均",
                                     "N" + key + "增长率"]]
                temp_res.rename(
                    columns={
                        "N" + key: "24年N" + key,
                        "N" + key + "_pre": "23年N" + key
                    },
                    inplace=True
                )

                # if len(temp_res) > 0:
                temp_res.to_excel(writer, sheet_name=sheet_name, index=False)

                    # 设置百分数格式

        # print("ok")

    # ******************************* 单年报审核 ************************************* #
    ## *************************** 经营成本 一二三产存疑   ***************************** #

    def 经营成本一二三产存疑_总收入_未扣除生产费用(self):
        nb_now = self.now_df.copy()
        zb = self.nb_zb

        # 经营 一二三产存疑
        # 收入对应指标
        in_zb = zb[(zb["代码"] >= 4012) & (zb["代码"] <= 4069)].reset_index(drop=True)
        # 删除多余的 4013
        in_zb = in_zb.drop(labels=1)
        in_zb_arr = list(in_zb["代码"])
        # 支出对应指标
        out_zb = zb[(zb["代码"] >= 4332) & (zb["代码"] <= 4367)]
        index = 0
        with pd.ExcelWriter(f"{self.out_path}/经营成本一二三产存疑(总收入).xlsx") as writer:
            for out in out_zb.itertuples():
                sheet_name = out[1].strip()
                if '支出' in sheet_name:
                    sheet_name = sheet_name[0:sheet_name.find("支出")]
                    # print(sheet_name)
                temp_key = f"N{out[3]}/N{in_zb_arr[index]}比值"
                nb_now[temp_key] = nb_now[f"N{out[3]}"] / nb_now[f"N{in_zb_arr[index]}"]
                temp_res = nb_now[(nb_now[temp_key] < 0.2) | (nb_now[temp_key] > 0.85)]
                temp_res = temp_res[["sID", "coun", "sCode", "N0205", f"N{out[3]}", f"N{in_zb_arr[index]}", temp_key]]

                if len(temp_res) > 0:
                    temp_res.to_excel(writer, sheet_name=sheet_name, index=False)

                index += 1

        return True

    def 经营成本一二三产存疑_现金收入_未扣除生产费用(self):
        nb_now = self.now_df
        zb = self.nb_zb

        # 经营 一二三产存疑
        # 收入对应指标
        in_zb = zb[(zb["代码"] >= 1522) & (zb["代码"] <= 1579)]
        in_zb_arr = list(in_zb["代码"])
        # 支出对应指标
        out_zb = zb[(zb["代码"] >= 1812) & (zb["代码"] <= 1847)]
        index = 0
        with pd.ExcelWriter(f"{self.out_path}/经营成本一二三产存疑（现金收入）.xlsx") as writer:
            for out in out_zb.itertuples():
                sheet_name = out[1].strip()
                if '支出' in sheet_name:
                    sheet_name = sheet_name[0:sheet_name.find("支出")]
                    # print(sheet_name)
                temp_key = f"N{out[3]}/N{in_zb_arr[index]}比值"
                nb_now[temp_key] = nb_now[f"N{out[3]}"] / nb_now[f"N{in_zb_arr[index]}"]
                temp_res = nb_now[(nb_now[temp_key] < 0.2) | (nb_now[temp_key] > 0.85)]
                temp_res = temp_res[["sID", "coun", "sCode", "N0205", f"N{out[3]}", f"N{in_zb_arr[index]}", temp_key]]

                if len(temp_res) > 0:
                    temp_res.to_excel(writer, sheet_name=sheet_name, index=False)

                index += 1
            # print()
        # with pd.ExcelWriter("../data/审核数据/经营一二三产存疑.xlsx") as writer:

        return True
